IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsMSSQLMemoryWork' AND type = 'P')
     DROP PROCEDURE dbo.InsMSSQLMemoryWork
GO

CREATE PROCEDURE dbo.InsMSSQLMemoryWork
     @PackageExecutionId tinyint,
     @SrvrInstncId       int, 
     @MachineName        varchar(128)
AS
/*
 *
 * InsMSSQLMemoryWork
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 10/03/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     IF EXISTS ( SELECT 'X'
                 FROM   tempdb.dbo.tempMSSQLRunningMachine
                 WHERE  PackageExecutionId = @PackageExecutionId
                 AND    SrvrInstncId       = @SrvrInstncId )
     BEGIN -- Update record
          UPDATE tempdb.dbo.tempMSSQLRunningMachine
          SET    MachineName = @MachineName
          FROM   tempdb.dbo.tempMSSQLRunningMachine
          WHERE  PackageExecutionId = @PackageExecutionId
          AND    SrvrInstncId       = @SrvrInstncId           
     END
     ELSE 
     BEGIN -- Insert Record
          INSERT tempdb.dbo.tempMSSQLRunningMachine
               ( PackageExecutionId,
                 SrvrInstncId,
                 MachineName, 
                 CrDt )
          SELECT @PackageExecutionId,
                 @SrvrInstncId, 
                 @MachineName,
                 CURRENT_TIMESTAMP
     END
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Inserts a record in the persisted temp table in tempdb used for setting memory limits for MSSQL Instances in a cluster.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = InsMSSQLMemoryWork;
GO
